package cn.gson.crm.model.dao;

import cn.gson.crm.model.pojos.Contract;
import cn.gson.crm.model.pojos.Employee;
import cn.gson.crm.model.pojos.Order;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface OrderDao extends CrudRepository<Order,Integer> {
    //查询统计订单金额(年分)
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY) FROM ORDERO_ o LEFT JOIN EMPLOYEE e on o.ORDER_CHARGE=e.YG_ID WHERE e.YG_ID=?1 and TO_CHAR(ORDER_TIME,'yyyy') like ?2")
    public Integer selectone(Employee employee, String order);

    //查询统计订单金额（月份）
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY) FROM ORDERO_ o LEFT JOIN EMPLOYEE e on o.ORDER_CHARGE=e.YG_ID WHERE e.YG_ID=?1 and TO_CHAR(ORDER_TIME,'mm') like ?2")
    public Integer selecttwo(Employee employee, String order);

    //查询统计订单金额(日期)
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY) FROM ORDERO_ o LEFT JOIN EMPLOYEE e on o.ORDER_CHARGE=e.YG_ID WHERE e.YG_ID=?1 and TO_CHAR(ORDER_TIME,'dd') like ?2")
    public Integer selectthere(Employee employee, String order);

    //查询统计回款金额(年份)
    @Query(nativeQuery = true,value = "SELECT sum(REFUND_AMOUNT) from ORDERO_  WHERE TO_CHAR(ORDER_TIME,'yyyy') LIKE ?1")
    public Integer selectfour(String order);
    //查询统计订单金额(年分)
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY) from ORDERO_  WHERE TO_CHAR(ORDER_TIME,'yyyy') LIKE ?1")
    public Integer selectfive(String orderTime);
    //查询统计回款金额(年分2)
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY),ORDER_TIME from ORDERO_  WHERE TO_CHAR(ORDER_TIME,'yyyy') LIKE ?1 GROUP BY ORDER_TIME")
    public Integer selectsix(String order);
    //查询统计订单金额(年分2)
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY),ORDER_TIME from ORDERO_  WHERE TO_CHAR(ORDER_TIME,'yyyy') LIKE ?1 GROUP BY ORDER_TIME")
    public Integer selectseven(String orderTime);
    //查询统计回款金额(日期)
    @Query(nativeQuery = true,value = "SELECT sum(REFUND_AMOUNT) from ORDERO_  WHERE TO_CHAR(ORDER_TIME,'dd') LIKE ?1")
    public Integer selecteight(String order);
    //查询统计订单金额（日期）
    @Query(nativeQuery = true,value = "SELECT sum(ORDER_MONEY) from ORDERO_  WHERE TO_CHAR(ORDER_TIME,'dd') LIKE ?1")
    public Integer selectnine(String orderTime);
    //主页指针（总订单金额）
    @Query(nativeQuery = true,value = "SELECT sum(a.ORDER_MONEY) from ORDERO_ a LEFT JOIN EMPLOYEE e on a.ORDER_CHARGE=e.YG_ID ")
    public Integer selectMoney(Long ordermoney);
   //主页指针(总付清金额)
    @Query(nativeQuery = true,value = "SELECT sum(a.ORDER_MONEY) from ORDERO_ a LEFT JOIN EMPLOYEE e on a.ORDER_CHARGE=e.YG_ID WHERE a.ORDER_FUQINFOU='已付清' ")
    public Integer selectMoney2(Long ordermoney);
    //主页指针(总订单回款金额)
    @Query(nativeQuery = true,value = "SELECT sum(a.REFUND_AMOUNT) from ORDERO_ a LEFT JOIN EMPLOYEE e on a.ORDER_CHARGE=e.YG_ID ")
    public Integer selectMoney3(Long refundamount);
    //主页指针(总订单付清回款金额)
    @Query(nativeQuery = true,value = "SELECT sum(a.REFUND_AMOUNT) from ORDERO_ a LEFT JOIN EMPLOYEE e on a.ORDER_CHARGE=e.YG_ID WHERE a.ORDER_FUQINFOU='已付清'")
    public Integer selectMoney4(Long refundamount);
    //主页周月分布
    @Query(nativeQuery = true,value = "SELECT sum(a.REFUND_AMOUNT) from ORDERO_ a LEFT JOIN EMPLOYEE e on a.ORDER_CHARGE=e.YG_ID WHERE a.ORDER_FUQINFOU='已付清' and TO_CHAR(ORDER_TIME,'yyyy-mm') like ?1")
    public Integer selectMoney5(String nian2);
    //主页月查询排行
    @Query(nativeQuery = true,value = "SELECT sum(a.REFUND_AMOUNT) from ORDERO_ a LEFT JOIN EMPLOYEE e on a.ORDER_CHARGE=e.YG_ID WHERE a.ORDER_FUQINFOU='已付清' and e.YG_ID=?1 and TO_CHAR(ORDER_TIME,'yyyy-mm') like ?2 group by e.YG_ID")
    public Integer selectMoney6(Employee employee,String nian2);
}
